#! /usr/bin/env python
#coding=utf-8

from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import time, sys
sys.path.insert(0, '../apps')
#from departments.models import *
#from charts.models import *
#from charts.models import *

import datetime
from datetime import date
from get_month_quarter import *

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()
#
#meta = MetaData(db)
#db.metadata.reflect()

def run():
    
    #从settings.ini获取任务类型编号
    settings = Ini('../apps/charts/settings.ini')


    #本脚本从任务书表取数据，按照任务所处阶段统计任务、子任务各阶段正常、异常及完成情况

    req = db.metadata.tables['requirement']                
    req_sys = db.metadata.tables['system']
    #获取本月月初和月末时间
    month_first_day,month_last_day,month_view = get_month()
#    print month_first_day,month_last_day,month_view
    #print month_last_day
    #计算本月新增准开发需求任务数，算法：开始日期小于或等于本月末and大于等于本月1日
    #new = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)]).group_by(req.c.kfzx)
    #zc统计状态为正常的各阶段阶段下任务书数据量
    #zc = select([req.c.type,req.c.stage,func.count(req.c.ass_id)],and_(req.c.status == '1',req.c.type.in_('1','2'))).group_by(req.c.type,req.c.stage)


    new = select([req.c.stage,func.count(req.c.id),req_sys.c.kfzx],
                  and_(req.c.created_date >= month_first_day,
                       req.c.created_date <= month_last_day,
                       req.c.main_sys == req_sys.c.id)).group_by(req_sys.c.kfzx,req.c.stage)
    #old = select([req.c.stage,func.count(req.c.id)],and_(req.c.created_date < month_first_day,req.c.begin_date >= month_first_day)).group_by(req.c.tx,req.c.stage)
    old = select([req.c.stage,func.count(req.c.req_id),req_sys.c.kfzx],
              or_(and_(req.c.created_date<month_first_day,
                       req.c.modified_date<month_first_day,
                      ~req.c.stage.in_(['19','1A','FN','CL']),
                       req.c.main_sys == req_sys.c.id),
                  and_(req.c.created_date<month_first_day,
                       req.c.modified_date<=month_last_day,
                       req.c.modified_date>=month_first_day,
                       req.c.main_sys == req_sys.c.id))).group_by(req_sys.c.kfzx,req.c.stage)

    #yc统计状态为延迟、异常终止、暂停的各阶段阶段下任务书数据量
    #yc = select([req.c.type,func.count(req.c.ass_id)],and_(req.c.status.in_('2','4','5'),req.c.type.in_('1','2'))).group_by(req.c.type,req.c.stage)
    old1 = select([req.c.stage,req.c.req_id,req_sys.c.kfzx],
              or_(and_(req.c.created_date<month_first_day,
                       req.c.modified_date<month_first_day,
                      ~req.c.stage.in_(['19','1A','FN','CL']),
                       req.c.main_sys == req_sys.c.id),
                  and_(req.c.created_date<month_first_day,
                       req.c.modified_date<=month_last_day,
                       req.c.modified_date>=month_first_day,
                       req.c.main_sys == req_sys.c.id))).group_by(req_sys.c.kfzx,req.c.stage)
    



    #wc统计状态为完成阶段阶段下本月任务书数据量

#    print old
    new_rs = new.execute()
    old_rs = old.execute()
    old1_rs = old1.execute()
    x = list(old1_rs)
#    print 'xxx',x
    old3_rs = old.execute()
    xx=list(old3_rs)
#    print 'yyy',xx
    #定义字典型data
    data = {}
    
    #定义两个字典，一个是阶段，一个是任务类型
    #types = ['1','2']
    #stages = ['total','ys_num_zc','ys_num_yc','cg_num_zc','cg_num_yc','ss_num_zc','ss_num_yc','tg_num_zc','tg_num_yc','yans_num_zc','yans_num_yc','js_num_zc','js_num_yc','wc_num_zc']
    ##
    stages = ['new_ky','new_ss','new_wc','new_yc','new_zt','old_ky','old_ss','old_wc','old_yc','old_zt','total']

    #初始化data字典 
    for y in stages:
        data[y] = 0
        data['month_date'] = month_last_day
        data['month'] = month_view
    #    data['type'] = x
    #    data['total'] = 0



    #data['total_num'] = 0
        
    #total_num = 0
    #以下两个for语句完成new_rs，old_rs中新增和续建准开发需求放在一条记录中                                                            


    for row in new_rs:
        stage, count,kfzx = row
        #print stage
        if stage == '' or stage == 'CL':
            continue
        stage = settings.PARA.new_map[stage]  
        #print stage,kfzx,data
        data[stage] += count
        data['total'] += count
        data['kfzx'] = kfzx

    for row in old_rs:
        stage, count,kfzx = row
        if not stage or stage == 'CL':
            continue        
        stage = settings.PARA.old_map[stage]
        data[stage] += count
        data['total'] += count
        data['kfzx'] = kfzx

           

    #将本月准开发需求汇总数据赋值给total_num


    req_model = get_model('requirement_month_report_stage')

    #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
    #db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
    req_model.remove(req_model.c.month_date == month_last_day)
    #print data
    # 按照任务类型存储统计数据
    #for v in data:
    #    print 't',t
    #    print 'v',v
    #    r = req_model(**v)
    #    v.save()
    #print data
    r = req_model(**data)
    r.save()

if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise


    
        
    
    

